using MongoDB.Bson;
using MongoDB.Driver;
using MongoDB.Driver.Linq;
using Sustainalytics.BusinessResearchProductStates.Entities.ResearchState;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.Entities;
using Sustainalytics.Entities.ProductInvolvement;
using Sustainalytics.MongoDB.Extensions;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using Sustainalytics.ESG.Entities;
using AnswerCategory = Sustainalytics.ClientDW.Entities.AnswerCategory;
using Sustainalytics.Utils;
using Sustainalytics.Entities.ScreeningTool;

namespace Sustainalytics.ClientDW.DataAccessLayer
{
    public class ClientDwRepository : IClientDwRepository
    {
        private static IClientDwRepository _clientDwRepository;
        private static IMongoDatabase _database;
        private static string _connectionString = string.Empty;

        private ClientDwRepository()
        {
        }

        public static IClientDwRepository GetInstance(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new ArgumentException(
                    "A connection string must be provided to create an IClientDwRepository instance!");

            _clientDwRepository = new ClientDwRepository();
            _clientDwRepository.SetDatabase(connectionString);

            return _clientDwRepository;
        }

        public void SetDatabase(string connectionString)
        {
            var url = MongoUrl.Create(connectionString);

            if (string.IsNullOrWhiteSpace(url.DatabaseName))
            {
                throw new ArgumentException("No database specified in the connection string!");
            }

            _connectionString = connectionString;
            _database = new MongoClient(url).GetDatabase(url.DatabaseName);
        }

        # region Corporate Data

        public CorporateData GetCorporateDataByCompanyId(Guid companyId)
        {
            return _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                            .AsQueryable()
                            .FirstOrDefault(cd => cd.Id == companyId);
        }

        public IList<CorporateData> ListCompaniesCorporateData(IEnumerable<Guid> companyIds)
        {
            return _database.GetCollection<CorporateData>()
                                .AsQueryable()
                                .Where(c => companyIds.Contains(c.Id))
                                .ToList();
        }
        
        public IList<CorporateData> ListParentCompaniesCorporateData()
        {
            var coverageCompanyIds = _database.GetCollection<CorporateData>()
                                .AsQueryable()
                                .Where(c => c.ResearchParentId != null)
                                .Select(x=> x.ResearchParentId.Value)
                                .ToList();

            return _database.GetCollection<CorporateData>()
                                .AsQueryable()
                                .Where(c => coverageCompanyIds.Contains(c.Id))                                
                                .ToList();
        }

        public List<FinancialData> ListCompaniesFinancialData(List<Guid> companyIds)
        {
            return _database.GetCollection<FinancialData>()
                                .AsQueryable()
                                .Where(c => companyIds.Contains(c.Id))
                                .ToList();
        }

        public List<Guid> GetCompanyIds()
        {
            return _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                .Find(FilterDefinition<CorporateData>.Empty)
                .Project(cd => cd.Id)
                .ToList();
        }

        public CorporateData GetCorporateDataByCapitalIQID(int capitalIqId) //Petre to do add unit tests
        {
            return _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                .AsQueryable()
                .FirstOrDefault(cd => cd.CapitalIqId == capitalIqId);
        }

        public List<CorporateData> GetCorporateDataByCapitalIQID(List<string> capitalIqIds) //Petre to do add unit tests
        {
            var parseContains = capitalIqIds.ToList().Select(ClientDWGlobalCompactRepository.ParseCapitalIQ).Where(a => a != 0).ToList();

            return _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                                .AsQueryable()
                                .Where(cd => parseContains.Contains(cd.CapitalIqId))
                                .ToList();
        }

        #endregion

        #region RP

        public IndicatorTemplate GetIndicatorTemplate(string number)
        {
            var orignalValue = _database.GetCollection<IndicatorTemplate>()
                                .Find(Builders<IndicatorTemplate>.Filter.Eq(c => c.Number, number))
                                .SingleOrDefault();

            //ESG Indicators - listing with value 0 and OFF on slider
            if (orignalValue != null)
            {
                orignalValue.AnswerCategories.Add(new AnswerCategory() { Score = -1, Text = "Off" });
            }

            return orignalValue;
        }

        public Dictionary<string, KeyValuePair<string, int>> ListThemeAndCategoryIdPerIndicatorNumber()
        {
            return _database.GetCollection<IndicatorTemplate>(typeof(IndicatorTemplate).Name)
                            .Find(_ => true)
                            .Project<IndicatorTemplate>(Builders<IndicatorTemplate>.Projection
                                .Include(it => it.Number)
                                .Include(it => it.ThemeName)
                                .Include(it => it.IndicatorCategorySortKey))
                            .ToList()
                            .ToDictionary(it => it.Number, it => new KeyValuePair<string, int>(it.ThemeName, it.IndicatorCategorySortKey));
        }

        public HashSet<IndicatorTemplate> ListIndicatorTemplatesIdAndNumber()
        {
            return new HashSet<IndicatorTemplate>(_database.GetCollection<IndicatorTemplate>(typeof(IndicatorTemplate).Name)
                                                .Find(_ => true)
                                                .ToList());
        }

        #endregion

        #region Events

        public List<BaseEventIndicator> GetBaseEventIndicators()
        {
            return _database.GetCollection<BaseEventIndicator>(typeof(BaseEventIndicator).Name)
                .Find(FilterDefinition<BaseEventIndicator>.Empty)
                .Project<BaseEventIndicator>(Builders<BaseEventIndicator>.Projection.Include(x => x.Id).Include(x => x.Name))
                .ToList();
        }

        #endregion

        # region Screening Tool

        public string GetAnswerCategoryText(string indicatorNumber, int value)
        {
            var document = _database.GetCollection<IndicatorTemplate>()
                .AsQueryable()
                .Where(f => f.Number == indicatorNumber)
                .Select(ans => new IndicatorTemplate() { AnswerCategories = ans.AnswerCategories })
                .SingleOrDefault();

            var strBuild = new StringBuilder();
            strBuild.Append("Minimum threshold is set to: ");
            if (document != null)
            {
                foreach (AnswerCategory answerCategoryText in document.AnswerCategories)
                {
                    if (answerCategoryText.Score.Value == value)
                    {
                        var currentText = answerCategoryText.Text.Value[0].ToString().ToLowerInvariant() + answerCategoryText.Text.Value.Substring(1);
                        strBuild.Append(currentText);
                        strBuild.Append(" or ");
                    }
                }
            }
            strBuild.Remove(strBuild.Length - 4, 3);
            return strBuild.ToString();
        }

        public List<string> LoadCriteriaTypeThreeData(string criteriaEntityField, string collectionName, List<int> universeIds, int skip, int take, string filter, out int totalCount)
        {
            var criteriaData = new List<string>();
            totalCount = 0;

            switch (criteriaEntityField)
            {
                case "Company Name":                    
                    criteriaData = _database.GetCollection<CorporateData>()
                        .AsQueryable()
                        .Where(c => c.CompanyName != null && c.CompanyName.ToLowerInvariant().Contains(filter.ToLowerInvariant()) && 
                        c.UniverseIds != null && c.UniverseIds.Any(x=>universeIds.Contains(x)))
                        .OrderBy(c => c.CompanyName)
                        .Select(c => c.CompanyName)
                        .Skip(skip).Take(take)
                        .ToList();
                    totalCount = (int)_database.GetCollection<CorporateData>()
                        .Count(Builders<CorporateData>.Filter.Where(
                            c => c.CompanyName != null &&
                            c.CompanyName.ToLowerInvariant().Contains(filter.ToLowerInvariant()) &&
                            c.UniverseIds != null &&
                            c.UniverseIds.Any(x => universeIds.Contains(x))
                            ));
                    break;

                case "Peer Group":
                    criteriaData = _database.GetCollection<PeerGroup>()
                                          .AsQueryable()
                                          .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                                          .OrderBy(c => c.Name)
                                          .Select(c => c.Name)
                                          .Skip(skip).Take(take).ToList();
                    totalCount = (int)_database.GetCollection<PeerGroup>()
                        .Count(Builders<PeerGroup>.Filter.Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant())));
                    break;
                case "Subindustry":
                    criteriaData = _database.GetCollection<CorporateData>()
                                          .AsQueryable()
                                          .Where(c => c.SubPeerGroup != null && c.SubPeerGroup.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                                          .GroupBy(c=>new { c.SubPeerGroupId, c.SubPeerGroup })
                                          .Select(d => new { SubPeerGroupId = d.Key.SubPeerGroupId, SubPeerGroup =d.Key.SubPeerGroup})
                                          .OrderBy(c => c.SubPeerGroup)
                                          .Select(c => c.SubPeerGroup)
                                          .Skip(skip).Take(take).ToList();
                    totalCount = (int)_database.GetCollection<CorporateData>()
                        .Count(Builders<CorporateData>.Filter.Where(c => c.SubPeerGroup != null && c.SubPeerGroup.ToLowerInvariant().Contains(filter.ToLowerInvariant())));
                    break;
                case "Region":
                    criteriaData = _database.GetCollection<Region>()
                                           .AsQueryable()
                                           .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                                           .OrderBy(c => c.Name).Select(f => f.Name)
                                           .Skip(skip).Take(take).ToList();
                    totalCount = (int)_database.GetCollection<Region>()
                        .Count(Builders<Region>.Filter.Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant())));
                    break;

                case "Country":
                    criteriaData = _database.GetCollection<Country>()
                                           .AsQueryable()
                                           .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                                           .OrderBy(c => c.Name).Select(f => f.Name)
                                           .Skip(skip).ToList();
                    totalCount = (int)_database.GetCollection<Country>()
                        .Count(Builders<Country>.Filter.Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant())));
                    break;

                case "Company type":
                    criteriaData = _database.GetCollection<CompanyType>()
                                           .AsQueryable()
                                           .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                                           .OrderBy(c => c.Name).Select(f => f.Name)
                                           .Skip(skip).Take(take).ToList();
                    totalCount = (int)_database.GetCollection<CompanyType>()
                        .Count(Builders<CompanyType>.Filter.Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant())));
                    break;
                case "Exchange":
                    criteriaData = _database.GetCollection<Exchange>()
                        .AsQueryable()
                        .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .OrderBy(c => c.Name).Select(f => f.Name)
                        .Skip(skip).Take(take).ToList();
                    totalCount = _database.GetCollection<Exchange>()
                        .AsQueryable()
                        .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .Select(c => c.Name).Count();
                    break;

                case "Ticker":
                    criteriaData = _database.GetCollection<Ticker>()
                        .AsQueryable()
                        .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .OrderBy(c => c.Name).Select(f => f.Name)
                        .Skip(skip).Take(take).ToList();
                    totalCount = _database.GetCollection<Ticker>()
                        .AsQueryable()
                        .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .Select(c => c.Name).Count();
                    break;

                case "ISIN":
                    criteriaData = _database.GetCollection<ISIN>()
                        .AsQueryable()
                        .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .OrderBy(c => c.Name).Select(f => f.Name)
                        .Skip(skip).Take(take).ToList();
                    totalCount = _database.GetCollection<ISIN>()
                        .AsQueryable()
                        .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .Select(c => c.Name).Count();
                    break;

                case "Sedol":
                    criteriaData = _database.GetCollection<Sedol>()
                        .AsQueryable()
                        .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .OrderBy(c => c.Name).Select(f => f.Name)
                        .Skip(skip).Take(take).ToList();
                    totalCount = _database.GetCollection<Sedol>()
                        .AsQueryable()
                        .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .Select(c => c.Name).Count();
                    break;

                case "CUSIP":
                    criteriaData = _database.GetCollection<CUSIP>()
                        .AsQueryable()
                        .Where(f => f.Name != null && f.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .OrderBy(c => c.Name).Select(f => f.Name)
                        .Skip(skip).Take(take).ToList();
                    totalCount = _database.GetCollection<CUSIP>()
                        .AsQueryable()
                        .Where(c => c.Name != null && c.Name.ToLowerInvariant().Contains(filter.ToLowerInvariant()))
                        .Select(c => c.Name).Count();
                    break;
            }

            return criteriaData;
        }

        public List<CriteriaBasic> LoadFilterTypeThreeSettings(string criteriaEntityField)
        {
            var criteriaData = new List<CriteriaBasic>();

            switch (criteriaEntityField)
            {
                case "Peer Group":
                    criteriaData = _database.GetCollection<PeerGroup>()
                                          .AsQueryable()
                                          .OrderBy(c => c.Name)
                                          .Select(c => new CriteriaBasic { Id = c.Id, Name = c.Name }).ToList();
                    break;
                case "Subindustry":
                    criteriaData = _database.GetCollection<CorporateData>()
                                          .AsQueryable()
                                          .GroupBy(c => new { c.SubPeerGroupId, c.SubPeerGroup })
                                          .Select(d => new { SubPeerGroupId = d.Key.SubPeerGroupId, SubPeerGroup = d.Key.SubPeerGroup })
                                          .OrderBy(c => c.SubPeerGroup)
                                          .Select(c => new CriteriaBasic { Id =  Guid.Parse(c.SubPeerGroupId.ToString()), Name = c.SubPeerGroup }).ToList();
                    break;
                case "Region":
                    criteriaData = _database.GetCollection<Region>()
                                           .AsQueryable()
                                           .OrderBy(c => c.Name)
                                           .Select(c => new CriteriaBasic { Id = c.Id, Name = c.Name }).ToList();
                    break;

                case "Country":
                    criteriaData = _database.GetCollection<Country>()
                                           .AsQueryable()
                                           .OrderBy(c => c.Name)
                                           .Select(c => new CriteriaBasic { Id = c.Id, Name = c.Name }).ToList();
                    break;

                case "Company type":
                    criteriaData = new List<CriteriaBasic>
                    {
                        new CriteriaBasic { IntId = 1, Name = "Public" },
                        new CriteriaBasic { IntId = 2, Name = "Private" },
                    };
                    break;
            }

            return criteriaData;
        }

        public Guid GetCompanyIdByCiqid(string ciqid)
        {
            var parseInt = ClientDWGlobalCompactRepository.ParseCapitalIQ(ciqid);

            return _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                .AsQueryable()
                .Where(cd => cd.CapitalIqId == parseInt)
                .Select(cd => cd.Id)
                .SingleOrDefault();
        }

        public Dictionary<string, Guid> GetCompanyIdByCiqid(List<string> ciqids)
        {
            if (ciqids == null || ciqids.Count == 0)
            {
                return new Dictionary<string, Guid>();
            }

            var parseList = new List<int>();
            foreach (var ciqid in ciqids)
            {
                var data = ClientDWGlobalCompactRepository.ParseCapitalIQ(ciqid);

                if (data != 0)
                {
                    parseList.Add(data);
                }
            }
            if (!parseList.Any() || parseList.All(d => d == 0))
            {
                return new Dictionary<string, Guid>();
            }

            var toUppers = parseList.Select(c => BsonValue.Create(c)).ToList();

            var cursor = _database.GetCollection<CorporateData>(typeof(CorporateData).Name)
                 .Find(Builders<CorporateData>.Filter.In(x => x.CapitalIqId, toUppers))
                 .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.CapitalIqId).Include(x => x.Id))
                 .ToList();

            return cursor.ToDictionary((cd) => cd.CapitalIqId.ToString(), (cd) => cd.Id, StringComparer.InvariantCultureIgnoreCase);
        }

        public IEnumerable<Guid> GetCoverageCompanies(IEnumerable<Guid> results)
        {
            var query = Builders<CorporateData>.Filter.Ne(a => a.ResearchParentId, null);

            return _database.GetCollection<CorporateData>("CorporateData")
                .Find(query)
                .Project<CorporateData>(Builders<CorporateData>.Projection.Include(x => x.Id).Include(x => x.ResearchParentId))
                .ToList()
                .Where(a =>
                    results.Contains(a.ResearchParentId.GetValueOrDefault()) &&
                    a.ResearchParentId.GetValueOrDefault() != Guid.Empty)
                .Select(a => a.Id);
        }

        public List<string> LoadCriteriaTypeSevenData(string criteriaEntityField,
            string collectionName, List<int> universeIds, int skip, int take, string filter, out int totalCount)
        {
            var criteriaData = new List<string>();
            totalCount = 0;

            if (criteriaEntityField == "OverallCompanyAssessment")
            {
                IEnumerable<string> items = new[]
                {
                    ((int)GCAssessment.Compliant).ToString(CultureInfo.InvariantCulture),
                    ((int)GCAssessment.NonCompliant).ToString(CultureInfo.InvariantCulture),
                    ((int)GCAssessment.Watchlist).ToString(CultureInfo.InvariantCulture)
                };

                if (!string.IsNullOrEmpty(filter))
                {
                    items = items.Where(gcAssessmentId =>
                    {
                        string gcAssessmentName = string.Empty;

                        switch (gcAssessmentId)
                        {
                            case "0":
                                gcAssessmentName = "Compliant";
                                break;

                            case "1":
                                gcAssessmentName = "Watchlist";
                                break;

                            case "2":
                                gcAssessmentName = "Non-compliant";
                                break;
                        }

                        return gcAssessmentName.ToLowerInvariant().Contains(filter.ToLowerInvariant());
                    });
                }

                totalCount = items.Count();

                criteriaData.AddRange(items);
            }

            return criteriaData;
        }
        #endregion
    }
}
